﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using DataAccess;
namespace DAO
{
    public class DaiLyDAO
    {
        public static String TaoMaDaiLy()
        {
            String strChu = "DL";
            String strSo = "0";
            String strMaDau = "DL001";

            String strSql = "SELECT TOP 1 MaDaiLy FROM DAILY ORDER BY MaDaiLy DESC";

            try
            {
                String strMaCuoi = DataConnection.ExecScalar(strSql).ToString();
                String strTemp = ((int.Parse(strMaCuoi.Remove(0, 2)) + 1)).ToString();
                String strMaDaiLy = strChu;
                while (strMaDaiLy.Length + strTemp.Length < 5)
                    strMaDaiLy += strSo;
                return strMaDaiLy + strTemp;

            }
            catch (Exception)
            {
                return strMaDau;
            }
        }
        public static List<DaiLyDTO> DanhSachDaiLyDangHoatDong()
        {
            List<DaiLyDTO> list = new List<DaiLyDTO>();
            string query = "SELECT MaDaiLy,TenDaiLy,DienThoai,DiaChi,NgayTiepNhan,Email,TenQuan,NoCuaDaiLy,TenLoaiDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy where TinhTrang=0";
            DataTable dt = DataConnection.ExecQuery(query);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    DaiLyDTO dl = new DaiLyDTO();
                    dl.MaDaiLy = dr["MaDaiLy"].ToString();
                    dl.TenDaiLy = dr["TenDaiLy"].ToString();
                    dl.DiaChi = dr["DiaChi"].ToString();
                    dl.NgayTiepNhan = dr["NgayTiepNhan"].ToString();
                    dl.Email = dr["Email"].ToString();
                    dl.TenQuan = dr["TenQuan"].ToString();
                    dl.DienThoai = dr["DienThoai"].ToString();
                    dl.NoCuaDaiLy = Convert.ToInt32(dr["NoCuaDaiLy"]);
                    dl.TenLoaiDaiLy = dr["TenLoaiDaiLy"].ToString();
                    list.Add(dl);
                }
            }
            return list;
        }
        public static DataTable LayDanhSachDaiLy1()
        {
            string query = "SELECT * FROM DAILY INNER JOIN QUAN ON DAILY.Quan = QUAN.MaQuan INNER JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }
        public static List<DaiLyDTO> LoadDanhSachDaiLy2()
        {
            List<DaiLyDTO> list = new List<DaiLyDTO>();
            string query = "SELECT MaDaiLy,TenDaiLy,DienThoai,DiaChi,NgayTiepNhan,Email,TenQuan,NoCuaDaiLy,TenLoaiDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy";
            DataTable dt = DataConnection.ExecQuery(query);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    DaiLyDTO dl = new DaiLyDTO();
                    dl.MaDaiLy = dr["MaDaiLy"].ToString();
                    dl.TenDaiLy = dr["TenDaiLy"].ToString();
                    dl.DiaChi = dr["DiaChi"].ToString();
                    dl.NgayTiepNhan = dr["NgayTiepNhan"].ToString();
                    dl.Email = dr["Email"].ToString();
                    dl.TenQuan = dr["TenQuan"].ToString();
                    dl.DienThoai = dr["DienThoai"].ToString();
                    dl.NoCuaDaiLy = Convert.ToInt32(dr["NoCuaDaiLy"]);
                    dl.TenLoaiDaiLy = dr["TenLoaiDaiLy"].ToString();
                    list.Add(dl);
                }
            }
            return list;
        }
        public static List<DaiLyDTO> LoadDanhSachDaiLy()
        {
            List<DaiLyDTO> list = new List<DaiLyDTO>();
            string query = "SELECT TenDaiLy,TenLoaiDaiLy,TenQuan,DienThoai,DiaChi,NgayTiepNhan,NoCuaDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy";
            DataTable dt = DataConnection.ExecQuery(query);
            if(dt.Rows.Count>0)
            {
                for (int i =0; i < dt.Rows.Count;i++ )
                {
                    DataRow dr = dt.Rows[i];
                    DaiLyDTO dl = new DaiLyDTO();
                    dl.TenDaiLy = dr["TenDaiLy"].ToString();
                    dl.TenLoaiDaiLy = dr["TenLoaiDaiLy"].ToString();
                    dl.TenQuan = dr["TenQuan"].ToString();
                    dl.DienThoai = dr["DienThoai"].ToString();
                    dl.DiaChi = dr["DiaChi"].ToString();
                    dl.NgayTiepNhan = dr["NgayTiepNhan"].ToString();
                    dl.NoCuaDaiLy = Convert.ToInt32(dr["NoCuaDaiLy"]);
                    list.Add(dl); 
                }
            }
            return list;
        }
        public static DataTable LoadDanhSachDaiLyTheoTenDaiLy(string tenDaiLy)
        {
            
            string query = "SELECT MaDaiLy,TenDaiLy,TenLoaiDaiLy,TenQuan,DienThoai,DiaChi,NgayTiepNhan,NoCuaDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy WHERE TenDaiLy = N'" + tenDaiLy + "'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
            
        }
        public static int SoDaiLyToiDaTrongQuan(int MaQuan)
        {
            string query = "SELECT count(MaDaiLy) FROM DAILY WHERE Quan ='" + MaQuan + "'";
            int iSl = (int)DataConnection.ExecScalar(query);
            return iSl;
        }
        public static bool frmTiepNhanDaiLy(DaiLyDTO dl)
        {
            string query = "INSERT INTO DAILY (MaDaiLy,TenDaiLy,MaLoaiDaiLy,Quan,DiaChi,DienThoai,Email,NoCuaDaiLy,NgayTiepNhan,TinhTrang,MaNhanVien)";
            query += "VALUES('" + dl.MaDaiLy + "',N'" + dl.TenDaiLy + "','" + dl.MaLoaiDaiLy + "','" + dl.MaQuan + "',N'" + dl.DiaChi + "','" + dl.DienThoai + "','" + dl.Email + "','" + dl.NoCuaDaiLy + "','" + dl.NgayTiepNhan + "','0','"+dl.MaNhanVien+"')";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return false;
            }
        }
        public static bool TiepNhanDaiLy(DaiLyDTO dl)
        {
            string query = "INSERT INTO DAILY (TenDaiLy,MaLoaiDaiLy,Quan,DiaChi,DienThoai,Email,NoCuaDaiLy,NgayTiepNhan)";
            query += "VALUES(N'" + dl.TenDaiLy + "','" + dl.MaLoaiDaiLy + "','" + dl.MaQuan + "',N'" + dl.DiaChi + "','" + dl.DienThoai + "','" + dl.Email + "','" + dl.NoCuaDaiLy + "','" + dl.NgayTiepNhan + "')";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}",e.Message);
                return false;
            }
        }
        public static DataTable TimKiemDaiLyCoBan(string tendaily)
        {

            string query = "SELECT MaDaiLy, TenDaiLy,Email,TenLoaiDaiLy,TenQuan,DiaChi,DienThoai,NgayTiepNhan,NoCuaDaiLy FROM DAILY INNER JOIN QUAN ON DAILY.Quan = QUAN.MaQuan INNER JOIN LOAIDAILY ON DAILY.MaLoaiDaiLy = LOAIDAILY.MaLoaiDaiLy WHERE TinhTrang ='0' AND TenDaiLy like N'%"+tendaily +"%'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;   
        }
        public static bool CapNhatDaiLy(DaiLyDTO dl)
        {
            string query = "UPDATE DAILY SET TenDaiLy = N'" + dl.TenDaiLy + "',Quan = '" + dl.MaQuan + "',MaLoaiDaiLy = '" + dl.MaLoaiDaiLy + "',DiaChi = N'" + dl.DiaChi + "',DienThoai = '" + dl.DienThoai + "',Email = '" + dl.Email + "',NoCuaDaiLy = '" + dl.NoCuaDaiLy + "',NgayTiepNhan = '" + dl.NgayTiepNhan + "'WHERE MaDaiLy ='" + dl.MaDaiLy + "'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access database",e.Message);
                return false;
            }
        }
        public static DataTable TimKiemNangCao(DaiLyDTO dl,int namTu,int namDen,int thangTu,int thangDen,int noTu,int NoDen,string loaidaily,string tenquan)
        {
            string query = "SELECT MaDaiLy,TenDaiLy,Email,TenLoaiDaiLy,TenQuan,DiaChi,DienThoai,NgayTiepNhan,NoCuaDaiLy FROM DAILY INNER JOIN QUAN ON DAILY.Quan = QUAN.MaQuan INNER JOIN LOAIDAILY ON DAILY.MaLoaiDaiLy = LOAIDAILY.MaLoaiDaiLy  WHERE TinhTrang='0'";

            if (namTu != -500)
             {

                 query += " AND YEAR(NgayTiepNhan)>='" + namTu + "'";
             }
            if (namDen != -500)
             {

                 query += " AND YEAR(NgayTiepNhan)<='" + namDen + "'";
             }
            if (thangTu != -500)
             {

                 query += " AND MONTH(NgayTiepNhan)>='" + thangTu + "'";
             }
            if (thangDen != -500)
             {

                 query += " AND MONTH(NgayTiepNhan)<='" + thangDen + "'";

             }
            if (noTu != -500)
             {

                 query += "AND NoCuaDaiLy >='" + noTu + "'";
             }
            if (NoDen != -500)
             {

                 query += "AND NoCuaDaiLy<='" + NoDen + "'";
             }
            if (loaidaily != "")
            {
                query += " AND TenLoaiDaiLy like N'%"+loaidaily+"'OR TenLoaiDaiLy like N'"+loaidaily+"%'";
            }
            if (tenquan != "")
            {
                query += " AND TenQuan like N'%"+tenquan+"'OR TenQuan like N'"+tenquan+"%'";
            }
             if (dl.TenDaiLy != "")
             {
                 query += " AND TenDaiLy like N'%"+dl.TenDaiLy +"'OR TenDaiLy like N'" + dl.TenDaiLy + "%'";
             }
             if (dl.DiaChi != "")
             {
                 query += " AND DiaChi like N'%" + dl.DiaChi + "'OR TenDaiLy like N'" + dl.DiaChi + "%'";
             }
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }
        public static List<DaiLyDTO> LoadDanhSachTenDaiLy()
        {
            List<DaiLyDTO> list = new List<DaiLyDTO>();
            string sql = "SELECT * FROM DAILY";
            DataTable kq = DataConnection.ExecQuery(sql);
            if (kq.Rows.Count > 0)
            {
                for (int i = 0; i < kq.Rows.Count; i++)
                {
                    DataRow dr = kq.Rows[i];
                    DaiLyDTO dd = new DaiLyDTO();
                    dd.TenDaiLy = dr["TenDaiLy"].ToString();
                    list.Add(dd);
                }
            }
            return list;
        }
        public static bool deleteDaiLy(string MaDaiLy)
        {
            string query = "DELETE FROM DAILY WHERE MaDaiLy = '"+MaDaiLy +"'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return false;
            }
        }

        public static List<DaiLyDTO> LoadDanhSachDaiLyCanXoa(int tinhtrang)
        {
            List<DaiLyDTO> list = new List<DaiLyDTO>();
            string query = "SELECT MaDaiLy,TenDaiLy,DienThoai,DiaChi,NgayTiepNhan,Email,TenQuan,NoCuaDaiLy,TenLoaiDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy where TinhTrang='"+tinhtrang+"'";
            DataTable dt = DataConnection.ExecQuery(query);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    DaiLyDTO dl = new DaiLyDTO();
                    dl.MaDaiLy = dr["MaDaiLy"].ToString();
                    dl.TenDaiLy = dr["TenDaiLy"].ToString();
                    dl.DiaChi = dr["DiaChi"].ToString();
                    dl.NgayTiepNhan = dr["NgayTiepNhan"].ToString();
                    dl.Email = dr["Email"].ToString();
                    dl.TenQuan = dr["TenQuan"].ToString();
                    dl.DienThoai = dr["DienThoai"].ToString();
                    dl.NoCuaDaiLy = Convert.ToInt32(dr["NoCuaDaiLy"]);
                    dl.TenLoaiDaiLy = dr["TenLoaiDaiLy"].ToString();
                    list.Add(dl);
                }
            }
            return list;
        }
        public static DataTable LoadDanhSachDaiLyCanXoa2(int tinhtrang)
        {

            string query = "SELECT MaDaiLy,TenDaiLy,DienThoai,DiaChi,NgayTiepNhan,Email,TenQuan,NoCuaDaiLy,TenLoaiDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy where TinhTrang='" + tinhtrang + "'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }

        public static bool HuyXoaGia(string maDaiLy)
        {
            string query = "UPDATE DAILY SET TinhTrang ='0' WHERE MaDaiLy ='" + maDaiLy + "'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access database", e.Message);
                return false;
            }
        }

        public static bool XoaGia(string maDaiLy)
        {
            string query = "UPDATE DAILY SET TinhTrang ='1' WHERE MaDaiLy ='" + maDaiLy + "'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access database", e.Message);
                return false;
            }
        }

        public static bool CapNhatGiaTien(string MaDaiLy, int TongTriGia)
        {
            string query = "UPDATE DAILY SET NoCuaDaiLy =NoCuaDaiLy-'" + TongTriGia + "' WHERE MaDaiLy ='" + MaDaiLy + "'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access database", e.Message);
                return false;
            }
        }

        public static bool CapNhatSoTienThu(string MaDaiLy, int SoTienThu)
        {
            string query = "UPDATE DAILY SET NoCuaDaiLy = NoCuaDaiLy +'" + SoTienThu + "' WHERE MaDaiLy ='" + MaDaiLy + "'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access database", e.Message);
                return false;
            }
        }

        public static DataTable NoCuaDaiLy(string p)
        {
            string query = "SELECT NoCuaDaiLy from DAILY where MaDaiLy='"+p+"'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }


        public static bool CapNhatNoCuaDaiLy(string p, int tongtienseno)
        {
            string query = "UPDATE DAILY SET NoCuaDaiLy ='"+tongtienseno+"' WHERE MaDaiLy ='"+p+"'";
            try
            {
               DataConnection.ExecNonQuery(query);
               return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access database", e.Message);
                return false;
            }
        }

        public static DataTable LayDanhSachDaiLyTheoMa(string madaily)
        {
            string query = "SELECT * FROM DAILY WHERE MaDaiLy='"+madaily+"'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }

        public static List<DaiLyDTO> LoadDanhSachDaiLyDangNo()
        {
            List<DaiLyDTO> list = new List<DaiLyDTO>();
            string query = "SELECT MaDaiLy,TenDaiLy,DienThoai,DiaChi,NgayTiepNhan,Email,TenQuan,NoCuaDaiLy,TenLoaiDaiLy FROM DAILY JOIN QUAN ON DAILY.Quan = QUAN.MaQuan JOIN LOAIDAILY ON DAILY.MaLoaiDaiLY = LOAIDAILY.MaLoaiDaiLy where NoCuaDaiLy>0";
            DataTable dt = DataConnection.ExecQuery(query);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    DaiLyDTO dl = new DaiLyDTO();
                    dl.MaDaiLy = dr["MaDaiLy"].ToString();
                    dl.TenDaiLy = dr["TenDaiLy"].ToString();
                    dl.DiaChi = dr["DiaChi"].ToString();
                    dl.NgayTiepNhan = dr["NgayTiepNhan"].ToString();
                    dl.Email = dr["Email"].ToString();
                    dl.TenQuan = dr["TenQuan"].ToString();
                    dl.DienThoai = dr["DienThoai"].ToString();
                    dl.NoCuaDaiLy = Convert.ToInt32(dr["NoCuaDaiLy"]);
                    dl.TenLoaiDaiLy = dr["TenLoaiDaiLy"].ToString();
                    list.Add(dl);
                }
            }
            return list;
        }
    }
}
